﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utility.Data.DbClient;

namespace Quick.CodeGenerator.Data
{
    public class MsSqlServer : IDataBase
    {
        Utility.DBUtility.DatabaseOperate.DbHelperSQL m_Sql = new Utility.DBUtility.DatabaseOperate.DbHelperSQL();
        /// <summary>
        /// 表字段名称
        /// </summary>
        public override string TableName { get { return "NAME"; } }
        /// <summary>
        /// 根据关键字,获取指定的表名
        /// </summary>
        /// <param name="keyWords"></param>
        /// <returns></returns>
        public override List<Model.TableInfo> GetTableListInfo(string keyWords)
        {
            string sql = @"SELECT 
                                NAME TABLENAME,
                                CASE XTYPE
                                    WHEN 'U'
                                        THEN 'TABLE'
                                    WHEN 'V'
                                        THEN 'VIEW'
                                END AS TABLETYPE
                            FROM 
                                SYSOBJECTS 
                            WHERE 
                                XTYPE IN ( 'U','V') ";

            string sqlWhere = CreateTableInfoSqlWhere(keyWords);
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sql += $" AND {sqlWhere} ";
            }
            return m_Sql.Query<Model.TableInfo>(sql, this.m_ConnectonString);
        }
        /// <summary>
        /// 获取指定表名的列数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override List<Model.ColumnInfo> GetColumnInfoList(string tableName)
        {
            string sql = @" SELECT COL.NAME AS COLUMN_NAME ,
                                   T.NAME AS DATA_TYPE ,
                                   COL.LENGTH AS DATA_LENGTH ,
                                   COL.PREC AS DATA_PRECISION,
                                   COLUMNPROPERTY(COL.ID, COL.NAME, 'Scale') AS DATA_SCALE ,
                                   CASE WHEN COL.ISNULLABLE = 1 THEN 'Y'
                                        ELSE 'N'
                                   END AS NULLABLE ,
                                   ISNULL(COMM.TEXT, '') AS DATA_DEFAULT,
                                   ISNULL(EP.[VALUE], '') AS COMMENTS ,
                                   OBJ.NAME AS TABLE_NAME,
                                   CASE WHEN EXISTS ( SELECT   1
                                                      FROM     DBO.SYSINDEXES SI
                                                               INNER JOIN DBO.SYSINDEXKEYS SIK ON SI.ID = SIK.ID
                                                                                         AND SI.INDID = SIK.INDID
                                                               INNER JOIN DBO.SYSCOLUMNS SC ON SC.ID = SIK.ID
                                                                                         AND SC.COLID = SIK.COLID
                                                               INNER JOIN DBO.SYSOBJECTS SO ON SO.NAME = SI.NAME
                                                                                         AND SO.XTYPE = 'PK'
                                                      WHERE    SC.ID = COL.ID
                                                               AND SC.COLID = COL.COLID ) THEN 'P'
                                        ELSE ''
                                   END AS CONSTRAINT_TYPE ,
                                   CASE WHEN  OBJ.XTYPE ='U' THEN 'TABLE'
                                       ELSE 'VIEW'
                                   END AS OBJECT_TYPE
                            FROM   
                                   DBO.SYSCOLUMNS COL
                                   LEFT  JOIN DBO.SYSTYPES T ON COL.XTYPE = T.XUSERTYPE
                                   INNER JOIN DBO.SYSOBJECTS OBJ ON COL.ID = OBJ.ID
                                                                   AND OBJ.XTYPE IN('U','V')
                                                                   AND OBJ.STATUS >= 0
                                   LEFT  JOIN DBO.SYSCOMMENTS COMM ON COL.CDEFAULT = COMM.ID
                                   LEFT  JOIN SYS.EXTENDED_PROPERTIES EP ON COL.ID = EP.MAJOR_ID
                                                                   AND COL.COLID = EP.MINOR_ID
                                                                   AND EP.NAME = 'MS_Description'
                                   LEFT  JOIN SYS.EXTENDED_PROPERTIES EPTWO ON OBJ.ID = EPTWO.MAJOR_ID
                                                                   AND EPTWO.MINOR_ID = 0
                                                                   AND EPTWO.NAME = 'MS_Description'
                            WHERE OBJ.NAME = @TABLE_NAME
                            ORDER BY COL.COLORDER ";
            DBParameter[] parameters = { new DBParameter("@TABLE_NAME", DBType.MsSql_VarChar, 50) };
            parameters[0].Value = tableName;
            return TransfromDbType2DbParameterType(m_Sql.Query<Model.ColumnInfo>(sql, this.m_ConnectonString, parameters));
        }
        /// <summary>
        /// 根据查询的数据类型转换成OracleDbType数据类型
        /// </summary>
        /// <param name="type">数据类型</param>
        /// <param name="isDecimal">是否是小数</param>
        /// <param name="length">数据长度</param>
        /// <returns></returns>
        public override string TransfromDbType2DbParameterType(string type, string decimalStr, string length)
        {
            bool isDecimal = !string.IsNullOrEmpty(decimalStr) && int.Parse(decimalStr) > 0;
            if (string.IsNullOrEmpty(type))
            {
                return string.Empty;
            }

            if (string.Equals(type, "INTEGER", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && !isDecimal))
            {

                return "DBType.Oracle_Long," + length;
            }
            else if (string.Equals(type, "DECIMAL", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && isDecimal))
            {
                return "DBType.Oracle_Decimal," + length;
            }
            else if (string.Equals(type, "date", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Date";
            }
            else if (string.Equals(type, "nvarchar2", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_NVarchar2," + length;
            }
            else if (string.Equals(type, "varchar2", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Varchar2," + length;
            }
            else if (string.Equals(type, "char", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Char," + length;
            }
            else if (string.Equals(type, "varchar", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Varchar," + length;
            }
            else if (string.Equals(type, "clob", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_Clob," + length;
            }
            else if (string.Equals(type, "nclob", StringComparison.OrdinalIgnoreCase))
            {
                return "DBType.Oracle_NClob," + length;
            }
            return "DBType.Oracle_NVarchar2," + length;
        }
        /// <summary>
        /// 根据查询的数据类型转换成C#数据类型
        /// </summary>
        /// <param name="type">数据类型</param>
		/// <param name="decimalStr">是否是小数</param>
        /// <param name="length">length</param>
		/// <param name="canNullStr">能否为空</param>
        /// <returns></returns>
        public override string TransfromDbType2CCsharp(string type, string decimalStr, string length, string canNullStr)
        {
            bool isDecimal = !string.IsNullOrEmpty(decimalStr) && int.Parse(decimalStr) > 0;
            bool canNull = "Y".Equals(canNullStr, StringComparison.OrdinalIgnoreCase);
            if (string.IsNullOrEmpty(type))
            {
                return string.Empty;
            }

            if (string.Equals(type, "INTEGER", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && !isDecimal))
            {

                return canNull ? "int?" : "int";
            }
            else if (string.Equals(type, "DECIMAL", StringComparison.OrdinalIgnoreCase)
                || (string.Equals(type, "NUMBER", StringComparison.OrdinalIgnoreCase) && isDecimal))
            {
                return canNull ? "decimal?" : "decimal";
            }
            else if (string.Equals(type, "date", StringComparison.OrdinalIgnoreCase))
            {
                return canNull ? "DateTime?" : "DateTime";
            }
            else if (string.Equals(type, "nvarchar2", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "varchar2", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "char", StringComparison.OrdinalIgnoreCase)
                  || string.Equals(type, "varchar", StringComparison.OrdinalIgnoreCase))
            {
                return "string";
            }

            return "string";
        }
    }
}
